--------------------------------------------------------------------------------
-- @name: sort
-- @author: dion cho
-- @note: test case for monitoring sort
--------------------------------------------------------------------------------

@capture_on
set echo on
set pages 10000
set lines 200
set timing off
set trimspool on
set serveroutput off

drop table t1 purge;

create table t1(c1 char(1000));

-------------------------------------------------------------
-- case 0: what is the minimal sort area size & optimal
alter session set workarea_size_policy = manual;

alter session set sort_area_size = 0;
alter session set sort_area_size = 0;


-- plan statistics
select /*+ gather_plan_statistics */ * 
from (
	select rownum as r, c1
	from t1 
	order by c1
) where r = 1;


select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

-- sql workarea
col prev_sql_id new_value sql_id
col prev_child_number new_value child_number

select /*+ gather_plan_statistics */ * 
from (
	select rownum as r, c1
	from t1 
	order by c1
) where r = 1;


select prev_sql_id, prev_child_number
from v$session
where sid = userenv('sid');

set serveroutput on
exec print_table('select * from v$sql_workarea where sql_id = ''&sql_id'' -
			and child_number = &child_number');
set serveroutput off	


-- 10032, 10033 event
exec tpack.begin_diag_trace(userenv('sid'), 10032, 1);

select /*+ gather_plan_statistics */ * 
from (
	select rownum as r, c1
	from t1 
	order by c1
) where r = 1;


exec tpack.end_diag_trace(userenv('sid'), 10032);

select * from table(tpack.get_diag_trace(userenv('sid')));


---------------------------------------------------------------------
-- case 2: for onepass

insert into t1
select level from dual
connect by level <= 10000;

commit;


alter session set workarea_size_policy = manual;

alter session set sort_area_size = 10485760;
alter session set sort_area_size = 10485760;


-- plan statistics
select /*+ gather_plan_statistics */ * 
from (
	select rownum as r, c1
	from t1 
	order by c1
) where r = 10000;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));


-- sql workarea
col prev_sql_id new_value sql_id
col prev_child_number new_value child_number
col prev_sql_addr new_value sql_addr

select /*+ gather_plan_statistics */ * 
from (
	select rownum as r, c1
	from t1 
	order by c1
) where r = 10000;

select prev_sql_id, prev_child_number, prev_sql_addr
from v$session
where sid = userenv('sid');

set serveroutput on
exec print_table('select * from v$sql_workarea where sql_id = ''&sql_id'' -
			and child_number = &child_number');
set serveroutput off	


-- 10032, 10033 event
exec tpack.begin_diag_trace(userenv('sid'), 10032, 1);

select /*+ gather_plan_statistics */ * 
from (
	select rownum as r, c1
	from t1 
	order by c1
) where r = 10000;

exec tpack.end_diag_trace(userenv('sid'), 10032);

select * from table(tpack.get_diag_trace(userenv('sid')));


exec tpack.begin_diag_trace(userenv('sid'), 10033, 1);

select /*+ gather_plan_statistics */ * 
from (
	select rownum as r, c1
	from t1 
	order by c1
) where r = 10000;

exec tpack.end_diag_trace(userenv('sid'), 10033);

select * from table(tpack.get_diag_trace(userenv('sid')));

-- session snapshot report
exec tpack.begin_session_snapshot;

select /*+ gather_plan_statistics */ * 
from (
	select rownum as r, c1
	from t1 
	order by c1
) where r = 10000;

exec tpack.add_session_snapshot;

col item format a50
select type, item, total_delta from table(tpack.session_snapshot_report);


-- 10046
exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);

select /*+ gather_plan_statistics */ * 
from (
	select rownum as r, c1
	from t1 
	order by c1
) where r = 10000;

exec tpack.end_diag_trace(userenv('sid'), 10046);

select * from table(tpack.get_diag_trace);



---------------------------------------------------------------------
-- case 2: for multipass
alter session set workarea_size_policy = manual;

alter session set sort_area_size = 524288;
alter session set sort_area_size = 524288;

-- plan statistics
select /*+ gather_plan_statistics */ * 
from (
	select rownum as r, c1
	from t1 
	order by c1
) where r = 10000;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));


-- sql workarea
col prev_sql_id new_value sql_id
col prev_child_number new_value child_number
col prev_sql_addr new_value sql_addr

select /*+ gather_plan_statistics */ * 
from (
	select rownum as r, c1
	from t1 
	order by c1
) where r = 10000;

select prev_sql_id, prev_child_number, prev_sql_addr
from v$session
where sid = userenv('sid');

set serveroutput on
exec print_table('select * from v$sql_workarea where sql_id = ''&sql_id'' -
			and child_number = &child_number');
set serveroutput off	


-- 10032, 10033 event
exec tpack.begin_diag_trace(userenv('sid'), 10032, 1);

select /*+ gather_plan_statistics */ * 
from (
	select rownum as r, c1
	from t1 
	order by c1
) where r = 10000;

exec tpack.end_diag_trace(userenv('sid'), 10032);

select * from table(tpack.get_diag_trace(userenv('sid')));


-- session snapshot report
exec tpack.begin_session_snapshot;

select /*+ gather_plan_statistics */ * 
from (
	select rownum as r, c1
	from t1 
	order by c1
) where r = 10000;

exec tpack.add_session_snapshot;

col item format a50
select type, item, total_delta from table(tpack.session_snapshot_report);


-- 10046
exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);

select /*+ gather_plan_statistics */ * 
from (
	select rownum as r, c1
	from t1 
	order by c1
) where r = 10000;

exec tpack.end_diag_trace(userenv('sid'), 10046);

select * from table(tpack.get_diag_trace);


@capture_off
